package caat.framework.datamanager;

import caat.framework.exception.DAOException;
import caat.framework.exception.DatabaseConnectionFailureException;
import caat.framework.utils.FontUtil;
import caat.framework.exception.ExecuteStatementException;
import caat.framework.exception.ResultSetCallbackException;
import caat.framework.exception.SelectStatementException;
import caat.framework.exception.StoredProcedureException;

import java.io.*;

import java.sql.*;

import java.util.*;

import org.apache.commons.beanutils.PropertyUtils;

@SuppressWarnings({ "serial", "unused" })
public class AppDAO extends BaseObject {
    public AppDAO() {

    }
    
    /**
     *
     * @throws SelectStatementException
     * @throws DatabaseConnectionFailureException
     * @throws java.lang.IllegalArgumentException
     * @return
     * @param callback
     * @param parameters
     * @param sSelectStatement
     */
   
    @SuppressWarnings("rawtypes")
	public Collection executeSelectStatement(String sSelectStatement,
                                             Vector parameters,
                                             IResultSetCallback callback,Connection conn) throws IllegalArgumentException,
                                                                                 DatabaseConnectionFailureException,
                                                                                 SelectStatementException {
        if (sSelectStatement == null || sSelectStatement.length() == 0)
            throw new IllegalArgumentException("FrameworkDAO:executeSelectStatement() - invalid select statement");
        if (callback == null)
            throw new IllegalArgumentException("FrameworkDAO:executeSelectStatement() - null callback interface");
        Collection returnCollection = null;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sSelectStatement);
            if (parameters != null) {
                Parameter param = null;
                int index = 1;
                Object o = null;
                for (Enumeration e = parameters.elements();
                     e.hasMoreElements(); ) {
                    param = (Parameter)e.nextElement();
                    if (param.isInParameter()) {
                        o = param.getParameter();
                        if (o != null)
                            pstmt.setObject(index, o);
                        else
                            pstmt.setObject(index, o, -1);
                    }
                    index++;
                }
            }
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && callback != null) {
                try {
                    returnCollection =
                            callback.notifyResultSet(rs, sSelectStatement);
                } catch (ResultSetCallbackException rscbe) {
                    printMessage("FrameworkDAO:executeSelectStatement() - " +
                                 rscbe.getMessage());
                } finally {
                    this.close(rs);
                }
            }
        } catch (SQLException exc) {
            throw new SelectStatementException("FrameworkDAO:executeSelectStatement - " +
                                               exc.getMessage(), exc);
        } finally {
            this.close(pstmt);
            this.close(conn);
        }
        return returnCollection;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
	public Collection executeSelectStatement(String sSQL, Vector parameters,
                                             String strValueObject,
                                             Connection conn) throws IllegalArgumentException,
                                                                     DatabaseConnectionFailureException,
                                                                     SelectStatementException {
        
		ArrayList retValList = new ArrayList();
        PreparedStatement pstmt = null;
        Object vo = null;
        try {
            pstmt = conn.prepareStatement(sSQL);
            if (parameters != null) {
                Parameter param = null;
                int index = 1;
                Object o = null;
                for (Enumeration e = parameters.elements();
                     e.hasMoreElements(); ) {
                    param = (Parameter)e.nextElement();
                    if (param.isInParameter()) {
                        o = param.getParameter();
                        if (o != null)
                            pstmt.setObject(index, o);
                        else
                            pstmt.setObject(index, o, -1);
                    }
                    index++;
                }
            }
            ResultSet rs = null;
            try {
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    try {
                        vo = Class.forName(strValueObject).newInstance();
                        populateResultSetToBean(vo, rs);
                        retValList.add(vo);
                    } catch (InstantiationException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException exc) {
                throw new SelectStatementException("FrameworkDAO:executeSelectStatement - " +
                                                   exc.getMessage(), exc);
            } finally {
                this.close(rs);
            }
        } catch (SQLException exc) {
            throw new SelectStatementException("FrameworkDAO:executeSelectStatement - " +
                                               exc.getMessage(), exc);
        } finally {
            this.close(pstmt);
        }
        return retValList;
    }

    public Object findByPK(String sSQL,
                           String strValueObject,Connection connection) throws IllegalArgumentException,
                                                         DatabaseConnectionFailureException,
                                                         SelectStatementException {
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Object retVal = null;
        try {
            pstmt =
                    connection.prepareStatement(sSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                try {
                    retVal = Class.forName(strValueObject).newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
                populateResultSetToBean(retVal, rs);
            }
        } catch (SQLException exc) {
            throw new SelectStatementException("FrameworkDAO:executeSelectStatement - " +
                                               exc.getMessage(), exc);
        } finally {
            close(rs);
            close(pstmt);
            this.close(connection);
        }
        return retVal;
    }

  

    @SuppressWarnings("rawtypes")
	public Object findByPK(String sSQL, Vector parameters,
                           String strValueObject,
                           Connection connection) throws IllegalArgumentException,
                                                         DatabaseConnectionFailureException,
                                                         SelectStatementException {
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Object retVal = null;
        try {
            pstmt =
                    connection.prepareStatement(sSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
            if (parameters != null) {
                Parameter param = null;
                int index = 1;
                Object o = null;
                for (Enumeration e = parameters.elements();
                     e.hasMoreElements(); ) {
                    param = (Parameter)e.nextElement();
                    if (param.isInParameter()) {
                        o = param.getParameter();
                        if (o != null)
                            pstmt.setObject(index, o);
                        else
                            pstmt.setObject(index, o, -1);
                    }
                    index++;
                }
            }
            rs = pstmt.executeQuery();
            if (rs.next()) {
                try {
                    retVal = Class.forName(strValueObject).newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
                populateResultSetToBean(retVal, rs);
            }
        } catch (SQLException exc) {
            throw new SelectStatementException("FrameworkDAO:findByPK - " +
                                               exc.getMessage(), exc);
        } finally {
            close(rs);
            close(pstmt);
        }
        return retVal;
    }

    @SuppressWarnings("rawtypes")
	public ResultSet executeSelectStatement(String sSelectStatement,
                                            Vector parameters,
                                            Connection conn) throws DatabaseConnectionFailureException,
                                                                    SelectStatementException {
        if (sSelectStatement == null || sSelectStatement.length() == 0)
            throw new IllegalArgumentException("FrameworkDAO:executeSelectStatement() - invalid select statement");
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sSelectStatement);
            if (parameters != null) {
                Parameter param = null;
                int index = 1;
                Object o = null;
                for (Enumeration e = parameters.elements();
                     e.hasMoreElements(); ) {
                    param = (Parameter)e.nextElement();
                    if (param.isInParameter()) {
                        o = param.getParameter();
                        if (o != null)
                            pstmt.setObject(index, o);
                        else
                            pstmt.setObject(index, o, -1);
                    }
                    index++;
                }
            }
            rs = pstmt.executeQuery();
        } catch (SQLException exc) {
            throw new SelectStatementException("FrameworkDAO:executeSelectStatement - " +
                                               exc.getMessage(), exc);
        }
        return rs;
    }

    @SuppressWarnings("rawtypes")
	public Collection executeStoredProcedure(String sProcedureName,
                                             Vector parameters,
                                             IResultSetCallback callback,
                                             Connection connection) throws IllegalArgumentException,
                                                                           DatabaseConnectionFailureException,
                                                                           StoredProcedureException {
        if (sProcedureName == null || sProcedureName.length() == 0)
            throw new IllegalArgumentException("FrameworkDAO:executeStoredProcedure()");
        Collection returnCollection = null;
        String sSQL = null;
        CallableStatement stmt = null;
        try {
            sSQL =
buildSQLForStoredProcedure(sProcedureName, parameters, true);
            stmt = prepareCallableStatementHelper(connection, sSQL);
            assignParametersToStatement(stmt, parameters, true);
            ResultSet rs = executeCallableStatement(stmt);
            if (callback != null && rs != null)
                try {
                    returnCollection =
                            callback.notifyResultSet(rs, sProcedureName);
                } catch (ResultSetCallbackException rscbe) {
                    throw new StoredProcedureException("FrameworkDAO:executeStoredProcedure() - " +
                                                       rscbe.getMessage(),
                                                       null);
                } finally {
                    close(rs);
                }
        } catch (SQLException exc) {
            throw new StoredProcedureException(exc.getMessage(), exc);
        } finally {
            close(stmt);
        }
        return returnCollection;
    }

    @SuppressWarnings("rawtypes")
	public Clob executeStoredProcedure(String sProcedureName,
                                       Vector parameters,
                                       Connection connection) throws IllegalArgumentException,
                                                                     DatabaseConnectionFailureException,
                                                                     StoredProcedureException {
        if (sProcedureName == null || sProcedureName.length() == 0)
            throw new IllegalArgumentException("FrameworkDAO:executeStoredProcedure()");
        Clob retVal = null;
        String sSQL = null;
        CallableStatement stmt = null;
        try {
            sSQL =
buildSQLForStoredProcedure(sProcedureName, parameters, false);
            stmt = prepareCallableStatementHelper(connection, sSQL);
            assignParametersToStatement(stmt, parameters, false);
            ResultSet rs = executeCallableStatement(stmt);
            if (rs != null)
                try {
                    retVal = rs.getClob(0);
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    close(rs);
                }
        } catch (SQLException exc) {
            throw new StoredProcedureException(exc.getMessage(), exc);
        } finally {
            close(stmt);
        }
        return retVal;
    }
    @SuppressWarnings("rawtypes")
    public int executeStatement(String sSQL, Vector parameters,
                                Connection conn) throws IllegalArgumentException,
                                                        DatabaseConnectionFailureException,
                                                        ExecuteStatementException {
        if (sSQL == null || sSQL.length() == 0)
            throw new IllegalArgumentException("FrameworkDAO:executeStatement()");
        int rowCount = -1;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sSQL);
            if (parameters != null) {
                Parameter param = null;
                int index = 1;
                Object o = null;
                for (Enumeration e = parameters.elements();
                     e.hasMoreElements(); ) {
                    param = (Parameter)e.nextElement();
                    if (param.isInParameter()) {
                        o = param.getParameter();
                        if (o != null)
                            pstmt.setObject(index, o);
                        else
                            pstmt.setObject(index, o, -1);
                    }
                    index++;
                }

            }
            rowCount = pstmt.executeUpdate();
        } catch (SQLException exc) {
            throw new ExecuteStatementException("FrameworkDAO:executeStatement - " +
                                                exc.getMessage(), exc);
        } finally {
            this.close(pstmt);

        }
        return rowCount;
    }
    @SuppressWarnings({ "rawtypes", "unchecked" })
	public Collection executeSelectWithPaging(Connection conn, String sSQL,
                                              Vector parameters,
                                              String strValueObject,
                                              Integer page, Integer count,
                                              Integer[] totalCount) throws IllegalArgumentException,
                                                                           DatabaseConnectionFailureException,
                                                                           SelectStatementException {
        ArrayList retValList = new ArrayList();

        PreparedStatement pstmt = null;

        Object vo = null;
        try {
            pstmt =
                    conn.prepareStatement(sSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);
            if (parameters != null) {
                Parameter param = null;
                int index = 1;
                Object o = null;
                for (Enumeration e = parameters.elements();
                     e.hasMoreElements(); ) {
                    param = (Parameter)e.nextElement();
                    if (param.isInParameter()) {
                        o = param.getParameter();
                        if (o != null)
                            pstmt.setObject(index, o);
                        else
                            pstmt.setObject(index, o, -1);
                    }
                    index++;
                }
            }
            ResultSet rs = null;
            try {
                rs = pstmt.executeQuery();

                int startRow = 0;
                int endRow = Integer.MAX_VALUE;

                if (page != null && count != null) {
                    startRow = (page.intValue() - 1) * count.intValue();
                    endRow = startRow + count.intValue();
                }

                if (startRow == 0)
                    rs.beforeFirst();
                else
                    rs.absolute(startRow);

                while (rs.next() && rs.getRow() <= endRow) {
                    try {
                        vo = Class.forName(strValueObject).newInstance();
                        populateResultSetToBean(vo, rs);
                        retValList.add(vo);
                    } catch (InstantiationException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                }

                if (totalCount != null) {
                    rs.last();
                    totalCount[0] = new Integer(rs.getRow());
                }
            } catch (SQLException exc) {
                throw new SelectStatementException("FrameworkDAO:executeSelectWithPaging - " +
                                                   exc.getMessage(), exc);
            } finally {
                this.close(rs);
            }
        } catch (SQLException exc) {
            throw new SelectStatementException("FrameworkDAO:executeSelectWithPaging - " +
                                               exc.getMessage(), exc);
        } finally {
            this.close(pstmt);
        }
        return retValList;
    }

    @SuppressWarnings("rawtypes")
    protected void assignParametersToStatement(CallableStatement stmt,
                                               Vector parameters,
                                               boolean bResultsetFlag) throws SQLException {
        if (parameters != null && parameters.size() > 0) {
            int index = 1;
            Parameter param = null;
            Object o = null;
            if (bResultsetFlag)
                index = 2;
            for (Enumeration e = parameters.elements(); e.hasMoreElements();
            ) {
                param = (Parameter)e.nextElement();
                if (param.isInParameter()) {
                    o = param.getParameter();
                    if (o != null)
                        stmt.setObject(index, o);
                    else
                        stmt.setObject(index, o, -1);
                } else {
                    stmt.registerOutParameter(index, param.getType());
                }
                index++;
            }
        }
    }

    protected CallableStatement prepareCallableStatementHelper(Connection connection,
                                                               String sSQL) throws SQLException {
        CallableStatement cs = null;
        cs = connection.prepareCall(sSQL);
        return cs;
    }

    protected ResultSet executeCallableStatement(CallableStatement stmt) throws SQLException {
        ResultSet rs = null;
        if (stmt != null)
            rs = stmt.executeQuery();
        return rs;
    }

    @SuppressWarnings("rawtypes")
	protected String buildSQLForStoredProcedure(String sProcedureName,
                                                Vector parameters,
                                                boolean bResultsetFlag) {
        StringBuffer sSQL = new StringBuffer("BEGIN ");
        sSQL.append(sProcedureName);
        if (parameters != null && parameters.size() > 0 || bResultsetFlag) {
            sSQL.append("(");
            int size = 0;
            if (parameters != null)
                size = parameters.size();
            for (int i = 0; i < size; ) {
                sSQL.append("?");
                if (++i < size)
                    sSQL.append(", ");
            }
            if (bResultsetFlag) {
                if (size > 0)
                    sSQL.append(",?);");
                else
                    sSQL.append("?);");
            } else {
                sSQL.append(");");
            }
        }
        sSQL.append("END;");
        return sSQL.toString();
    }

   
    public static void populateResultSetToBean(Object beanTO, ResultSet rs,
                                               int enc) throws SQLException {

        StringBuffer logMsg = new StringBuffer("[populate]");
        ResultSetMetaData metaData = rs.getMetaData();
        int ncolumns = metaData.getColumnCount();

        String columnName = null;
        String typeName;
        Object value;
        // Scroll to next record and pump into hashmap
        try {
            for (int i = 1; i <= ncolumns; i++) {
                columnName = (metaData.getColumnName(i));
                if (!PropertyUtils.getPropertyType(beanTO,
                                                   columnName).equals("") &&
                    PropertyUtils.getPropertyType(beanTO, columnName) !=
                    null) {
                    typeName =
                            PropertyUtils.getPropertyType(beanTO, columnName).getName();
                    if (typeName.equalsIgnoreCase("long")) {
                        value = new Long(rs.getLong(i));
                    } else if (typeName.equalsIgnoreCase("double")) {
                        value = new Double(rs.getDouble(i));
                    } else if (typeName.equalsIgnoreCase("java.lang.String")) {
                        if (enc == EncodeUtil.ENCODING_TCVN3_TO_UTF8) {
                            value =
                                    EncodeUtil.convertFromTCVN3(getBinaryStream(rs,
                                                                                i));
                        } else if (enc == EncodeUtil.ENCODING_UTF8_TO_TCVN3) {
                            value =
                                    EncodeUtil.convertToTCVN3(getBinaryStream(rs,
                                                                              i));
                        } else {
                            value = rs.getString(i);
                        }
                    } else if (typeName.equalsIgnoreCase("java.sql.Date")) {
                        value = rs.getDate(i);
                    } else if (typeName.equalsIgnoreCase("java.lang.Long")) {
                        if (rs.getString(i) == null) {
                            value = null;
                        } else {
                            value = new Long(rs.getLong(i));
                        }
                    } else if (typeName.equalsIgnoreCase("java.lang.Double")) {
                        if (rs.getString(i) == null) {
                            value = null;
                        } else {
                            value = new Double(rs.getDouble(i));
                        }
                    }

                    else {
                        value = rs.getObject(i);
                    }
                    PropertyUtils.setProperty(beanTO, columnName, value);
                    logMsg.append("{" + columnName + "=" + value + "}");
                } else {
                    logMsg.append("{Khong co setter tuong ung: " + columnName +
                                  "}");
                }
            }
        } catch (Exception ex) {
            System.err.println(logMsg);
            throw new SQLException(ex.toString());
        }
    }

    public static void populateResultSetToBean(Object beanTO,
                                               ResultSet rs) throws SQLException {

        populateResultSetToBean(beanTO, rs, EncodeUtil.ENCODING_NO);
    }

    public static String getBinaryStream(ResultSet rs,
                                         int arg0) throws Exception {
        InputStream in;
        byte[] strByte;
        int idx;

        in = rs.getBinaryStream(arg0);

        if (in == null) {
            return null;
        }

        strByte = new byte[in.available()];
        idx = 0;
        while (in.available() != 0) {
            strByte[idx] = (byte)in.read();
            ++idx;
        }

        return new String(strByte);
    }
    // release Connection

    protected void close(Connection conn) {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn = null;
        }
    }
    // close ResultSet

    protected void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                rs = null;
            }

        }
    }

    // close PreparedStatement

    protected void close(PreparedStatement pstmt) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                pstmt = null;
            }

        }
    }

    // close Statement

    protected void close(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                stmt = null;
            }
        }
    }
    @SuppressWarnings("rawtypes")
    public Object executeFunction(String sFunctionName, Vector parameters,
                                  Connection connection) throws IllegalArgumentException,
                                                                DAOException,
                                                                DatabaseConnectionFailureException,
                                                                StoredProcedureException {
        if (sFunctionName == null || sFunctionName.length() == 0)
            throw new IllegalArgumentException("FrameworkDAO:executeStoredProcedure()");

        Object returnValues = null;
        CallableStatement stmt = null;
        String sSQL = null;
        try {
            sSQL = buildSQLForFunction(sFunctionName, parameters);
            stmt = connection.prepareCall(sSQL);
            assignParametersToStatement(stmt, parameters, false);
            stmt.execute();
            returnValues = stmt.getObject(1);
        } catch (SQLException exc) {
            throw new StoredProcedureException("FrameworkDAO:executeFunction - " +
                                               exc.getMessage(), exc);
        } finally {
            close(stmt);
        }
        return returnValues;
    }
    @SuppressWarnings("rawtypes")
    protected String buildSQLForFunction(String sFunctionName,
                                         Vector parameters) {
        StringBuffer sSQL = new StringBuffer("BEGIN ");
        sSQL.append("? := ");
        sSQL.append(sFunctionName);
        if (parameters != null && parameters.size() > 1) {
            sSQL.append("(");
            for (int i = 1; i < parameters.size(); ) {
                sSQL.append("?");
                if (++i < parameters.size())
                    sSQL.append(", ");
            }
            sSQL.append(")");
        }
        sSQL.append(";");
        sSQL.append("END;");
        return sSQL.toString();
    }

    @SuppressWarnings("rawtypes")
	public ResultSet executeSelectStatement(String sSelectStatement,
                                            Vector parameters, Connection conn,
                                            PreparedStatement pstmt) throws Exception {

        if (sSelectStatement == null || sSelectStatement.length() == 0) {
            throw new IllegalArgumentException("FrameworkDAO:executeSelectStatement() - invalid select statement");
        }

        pstmt = conn.prepareStatement(sSelectStatement);
        if (parameters != null) {
            Parameter param = null;
            int index = 1;
            Object o = null;
            for (Enumeration e = parameters.elements(); e.hasMoreElements();
            ) {
                param = (Parameter)e.nextElement();
                if (param.isInParameter()) {
                    o = param.getParameter();
                    if (o != null) {
                        pstmt.setObject(index, o);
                    } else {
                        pstmt.setObject(index, o, -1);
                    }
                }
                index++;
            }
        }
        return pstmt.executeQuery();
    }
    public static ResourceBundle getConnectionSql(String filenameResource){
		ResourceBundle	resourceBundle=null;
		try{
		resourceBundle = ResourceBundle
					.getBundle(filenameResource);	
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return resourceBundle;
	}	
}
